package com.monitor.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.monitor.entity.ErpMateriel;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 物料表 Mapper 接口
 * </p>
 *
 * @author LX
 * @since 2018-09-08
 */
public interface ErpMaterielMapper extends BaseMapper<ErpMateriel> {
	@Select("${cha}")
	public List<Map<String, Object>> selectIn(@Param("cha")String cha);

	@Update("update erp_materiel set materiel_storage_price= ${tt} ${where}")
	public Integer updatedd(@Param("tt")String tt,@Param("where")String where);

	@Select("SELECT DISTINCT ${cha} FROM erp_materiel where ${tiao}")
	public List<String> selectCha(@Param("cha")String cha,@Param("tiao")String tiao);
	
	@Select("SELECT * FROM erp_materiel WHERE materiel_production_id=#{productionId}")
	public ErpMateriel selectByProductionId(@Param("productionId")String productionId);
	
	@Select("select * from erp_materiel m where m.materiel_storage_date=#{time} and m.warehouse_id=#{warehouseId}")
	public List<ErpMateriel> selectByDateAndWareHouse(@Param("time")String time,@Param("warehouseId")int warehouseId);



	@Select("SELECT DISTINCT(t.materiel_production_id) from (select DISTINCT(materiel_production_id) materiel_production_id from erp_materiel UNION all select DISTINCT(materiel_production_id) materiel_production_id  from erp_sale_repair_sale ) t ")
	public List<Map<String, Object>> selectByWarehouseIdqb(@Param("warehouseId")String warehouseId);

	//查询所有物料  物料表信息
	@Select("SELECT   " +
			" max(any_value(m.id)) id,  m.materiel_name,   " +
			"  any_value(m.materiel_brand),   " +
			"  any_value(m.materiel_Specifications),  " +
			"  any_value(m.materiel_storage_price),any_value(m.materiel_place),  " +
			"  any_value(m.materiel_state),   " +
			"  any_value(m.materiel_unit),   " +
			"  any_value(m.materiel_model),   " +
			" sum(m.materiel_number) num,   " +
			"  group_concat(m.materiel_production_id) materiel_production_id,   " +
			"  max(m.materiel_storage_date) materiel_storage_date,   " +
			"  (select war_name from  erp_customer_warehouse where id =max(m.warehouse_id)) war_name,   " +
			"  group_concat(materiel_eqcode) materiel_eqcode,any_value(m.materiel_invoice_type),any_value(m.fenzu),  " +
			"  group_concat(m.materiel_rfid) materiel_rfid,group_concat(m.materiel_taiyaid) materiel_taiyaid ,any_value(m.iszhi_neng),any_value(m.istai),any_value(m.corresponding_id)" +
			" FROM   " +
			"  erp_materiel_copy m   " +
			" LEFT JOIN erp_customer_warehouse AS cw ON m.warehouse_id = cw.id   " +
			" WHERE   " +
			"  m.warehouse_id in(${warehouseId})   " +
			"  and m.materiel_load=0  and  m.kucunorliucun='库存'  and delect_type=1  " +
			" GROUP BY   " +
			"  m.materiel_name,   " +
			"  m.materiel_brand,   " +
			"  m.materiel_Specifications,   " +
			"  m.materiel_place,   " +
			"  m.materiel_state,   " +
			"  m.materiel_unit,   " +
			"  m.materiel_storage_price,m.materiel_invoice_type,m.fenzu, " +
			"  m.materiel_model order by m.id desc" )
	//erp_materiel
	public List<Map<String, Object>> selectByWarehouseId(@Param("warehouseId")String warehouseId);
	
	//查询所有物料 数据纠错  物料表信息
	@Select("SELECT m.*,w.war_name FROM erp_materiel as m LEFT JOIN erp_customer_warehouse as w ON m.warehouse_id=w.id WHERE m.warehouse_id in(${warehouseId})  and delect_type=1 and m.materiel_load=0 and  m.kucunorliucun='库存'  ${sql}  order by m.id desc" )
	public List<Map<String, Object>> selectErrorByWarehouseId(@Param("warehouseId")String warehouseId,@Param("sql")String sql);


	//查询销售维修查看库存物料 对规格等6种进行分组
	@Select("SELECT   \n" +
			"\t\t\t max(m.id) id,  m.materiel_name,   \n" +
			"\t\t\t  m.materiel_brand,   \n" +
			"\t\t\t  m.materiel_Specifications,   \n" +
			"\t\t\t m.materiel_place,   \n" +
			"\t\t\t  m.materiel_state,   \n" +
			"\t\t\t  m.materiel_unit,   \n" +
			"\t\t\t  m.materiel_model,   \n" +
			"\t\t\t  count(1) num,   \n" +
			"\t\t\t  group_concat(m.materiel_production_id) materiel_production_id,   \n" +
			"\t\t\t  max(m.materiel_storage_date) materiel_storage_date,   \n" +
			"\t\t\t  (select war_name from  erp_customer_warehouse where id =max(m.warehouse_id)) war_name,   \n" +
			"\t\t\t  group_concat(materiel_eqcode) materiel_eqcode,m.materiel_invoice_type,  \n" +
			"\t\t\t  group_concat(m.materiel_rfid) materiel_rfid,group_concat(m.materiel_taiyaid) materiel_taiyaid\n" +
			"\t\t\t FROM   \n" +
			"\t\t\t  erp_materiel m   \n" +
			"\t\t\t LEFT JOIN erp_customer_warehouse AS cw ON m.warehouse_id = cw.id   \n" +
			"\t\t\t WHERE   \n" +
			"\t\t\t m.kucunorliucun='库存'  and m.warehouse_id =${ckid}  ${sqlstr}\n" +
			"\t\t\t AND m.materiel_load=0  and m.id not in(select id from erp_materiel where corresponding_id is not null and corresponding_id!=0 and istai=0) \n" +
			"\t\t\t GROUP BY   \n" +
			"\t\t\t  m.materiel_name,   \n" +
			"\t\t\t  m.materiel_brand,   \n" +
			"\t\t\t  m.materiel_Specifications,   \n" +
			"\t\t\t  m.materiel_place,   \n" +
			"\t\t\t  m.materiel_state,   \n" +
			"\t\t\t  m.materiel_unit,   \n" +
			"\t\t\t  m.materiel_invoice_type,\n" +
			"\t\t\t  m.materiel_model order by m.id desc" )
	public List<Map<String, Object>> selectByWarehouseId_xswx(@Param("ckid")int ckid,@Param("sqlstr")String sqlstr);

	//查询销售维修 where  留存 仓库id  客户id
 	@Select("SELECT   \n" +
			"\t\t\t max(m.id) id,  m.materiel_name,   \n" +
			"\t\t\t  m.materiel_brand,   \n" +
			"\t\t\t  m.materiel_Specifications,   \n" +
			"\t\t\t m.materiel_place,   \n" +
			"\t\t\t  m.materiel_state,   \n" +
			"\t\t\t  m.materiel_unit,   \n" +
			"\t\t\t  m.materiel_model,   \n" +
			"\t\t\t  count(1) num,   \n" +
			"\t\t\t  group_concat(m.materiel_production_id) materiel_production_id,   \n" +
			"\t\t\t  max(m.materiel_storage_date) materiel_storage_date,   \n" +
			"\t\t\t  (select war_name from  erp_customer_warehouse where id =max(m.warehouse_id)) war_name,   \n" +
			"\t\t\t  group_concat(materiel_eqcode) materiel_eqcode,m.materiel_invoice_type,  \n" +
			"\t\t\t  group_concat(m.materiel_rfid) materiel_rfid,group_concat(m.materiel_taiyaid) materiel_taiyaid\n" +
			"\t\t\t FROM   \n" +
			"\t\t\t  erp_materiel m   \n" +
			"\t\t\t LEFT JOIN erp_customer_warehouse AS cw ON m.warehouse_id = cw.id   \n" +
			"\t\t\t WHERE   \n" +
			"\t\t\t m.kucunorliucun='留存'  AND m.materiel_load=0 and m.id not in(select id from erp_materiel where corresponding_id is not null and corresponding_id!=0 and istai=0) and m.warehouse_id =${ckid}  and m.customer_id=${khid} \n" +
			"\t\t\t   \n" +
			"\t\t\t GROUP BY   \n" +
			"\t\t\t  m.materiel_name,   \n" +
			"\t\t\t  m.materiel_brand,   \n" +
			"\t\t\t  m.materiel_Specifications,   \n" +
			"\t\t\t  m.materiel_place,   \n" +
			"\t\t\t  m.materiel_state,   \n" +
			"\t\t\t  m.materiel_unit,   \n" +
			"\t\t\t  m.materiel_invoice_type,\n" +
			"\t\t\t  m.materiel_model order by m.id desc" )
	public List<Map<String, Object>> getMateriels_xswx_lc(@Param("ckid")int ckid,@Param("khid")int khid);

	//查询消耗维修物料
	@Select("select " +
			"m.materiel_name, " +
			"m.materiel_brand," +
			"m.materiel_Specifications," +
			"m.materiel_place," +
			"m.materiel_state," +
			"m.materiel_unit," +
			"m.materiel_model,count(1) num,max(m.materiel_production_id) materiel_production_id" +
			" from erp_materiel m LEFT JOIN erp_customer_warehouse AS cw ON m.warehouse_id = cw.id " +
			"where  m.warehouse_id in(${warehouseId}) and  m.delect_type = '1' and m.kucunorliucun='库存' and m.materiel_name in (select name from  erp_specifications where  pid='SF01543141048407') " +
			"GROUP BY m.materiel_name," +
			"m.materiel_brand," +
			"m.materiel_Specifications," +
			"m.materiel_place," +
			"m.materiel_state," +
			"m.materiel_unit," +
			"m.materiel_model " )
	public List<Map<String, Object>> selectByWarehouseType(@Param("warehouseId")String warehouseId);

	@Select("SELECT DISTINCT cw.war_name,m.id,m.materiel_taiyaid,m.materiel_storage_date,m.materiel_model,m.materiel_name,m.materiel_brand,m.materiel_Specifications,m.materiel_place,m.materiel_state,\t(m.materiel_number-( SELECT IFNULL( SUM( fs.materiel_storage_number ), 0 ) FROM erp_materiel_flow_summary AS fs WHERE fs.materiel_id = m.id AND materiel_flow_type=1 )+(SELECT IFNULL( SUM( fs.materiel_storage_number ), 0 ) FROM erp_materiel_flow_summary AS fs WHERE fs.materiel_id = m.id AND materiel_flow_type=0)) AS num,m.materiel_production_id,m.materiel_storage_price,m.materiel_invoice_type,m.materiel_eqcode,m.materiel_rfid,m.materiel_unit FROM erp_materiel as m LEFT JOIN erp_customer_warehouse as cw ON m.warehouse_id=cw.id  WHERE m.warehouse_id in(${warehouseId}) and m.materiel_name='${materiel_name}' and m.materiel_brand='${materiel_brand}' and m.materiel_Specifications='${materiel_Specifications}' and m.materiel_model='${materiel_model}' and m.materiel_place='${materiel_place}' and m.materiel_unit='${materiel_unit}' and  m.delect_type = '1' and m.kucunorliucun='${typekcorlc}' and materiel_load = 0 ${sqlstr}  ")
	public List<Map<String, Object>> selectByWarehouseIdzi(@Param("sqlstr")String sqlstr,@Param("warehouseId")String warehouseId,@Param("materiel_name")String materiel_name,@Param("materiel_brand")String materiel_brand,@Param("materiel_Specifications")String materiel_Specifications,@Param("materiel_model")String materiel_model,@Param("materiel_place")String materiel_place,@Param("materiel_unit")String materiel_unit,@Param("typekcorlc")String typekcorlc);


	@Select("SELECT m.* from erp_materiel m where m.materiel_load=1 and m.warehouse_id in (select cw.id from erp_customer_warehouse cw where cw.customer_id in (select c.id from erp_customer c where c.id   in (${cusIds})))")
	public List<ErpMateriel> selectWorkTime(@Param("cusIds")String cusIds);
	
	@Select("SELECT * from erp_materiel m where m.materiel_production_id=#{productionId} and m.materiel_name like '%轮胎%'  and m.materiel_brand=#{brand} and m.materiel_Specifications=#{specifications}")
	public ErpMateriel selectByProductionIdAndBrand(@Param("productionId")String productionId,@Param("brand")String brand,@Param("specifications")String specifications);
	
	@Select("SELECT * from erp_materiel m where m.materiel_production_id=#{productionId} and m.warehouse_id in ( select cw.id from erp_customer_warehouse cw where cw.customer_id=#{custId} ) and m.materiel_name like '%传感器%'")
	public ErpMateriel selectByProductionIdAndCustId(@Param("productionId")String productionId,@Param("custId")int custId);
	
	@Select("SELECT SUM( mfs.materiel_storage_number - mfs.materiel_outflow_number ) as num, any_value(mfs.materiel_id), any_value(mfs.warehouse_id), any_value(cw.materiel_name), any_value(cw.materiel_brand), any_value(cw.materiel_specifications), any_value(cw.materiel_model), any_value(cw.materiel_place), any_value(cw.materiel_state), any_value(cw.materiel_unit), any_value(cw.materiel_warn_amount), any_value(mfs.materiel_outflow_number) FROM erp_materiel_flow_summary mfs, erp_warehouse cw WHERE mfs.warehouse_id IN ( ${wareIds} )  AND cw.id = mfs.materiel_id ${name} AND mfs.materiel_flow_date <  #{startTime} AND ( mfs.materiel_retained != 1 OR mfs.materiel_retained IS NULL ) AND mfs.flow_subject != '车辆物料' GROUP BY cw.materiel_name, cw.materiel_brand, cw.materiel_specifications, cw.materiel_model, cw.materiel_place, cw.materiel_state, cw.materiel_unit HAVING SUM( mfs.materiel_storage_number - mfs.materiel_outflow_number ) >=0")
	public List<Map<String, Object>> selectByWareIdsAndName(@Param("wareIds")String wareIds,@Param("name")String name,@Param("startTime")String startTime);
	
	@Select("SELECT" + 
			"	SUM( mfs.materiel_storage_number ) as rk," + 
			"	SUM( mfs.materiel_outflow_number ) as ck," + 
			"	any_value(mfs.materiel_id)," + 
			"	any_value(mfs.warehouse_id)," + 
			"	any_value(cw.materiel_name)," + 
			"	any_value(cw.materiel_brand)," + 
			"	any_value(cw.materiel_specifications)," + 
			"	any_value(cw.materiel_model)," + 
			"	any_value(cw.materiel_place)," + 
			"	any_value(cw.materiel_state)," + 
			"	any_value(cw.materiel_unit)," + 
			"	any_value(cw.materiel_warn_amount)," + 
			"	any_value(mfs.materiel_storage_number)," + 
			"	any_value(mfs.materiel_storage_price) " + 
			"FROM" + 
			"	erp_materiel_flow_summary mfs," + 
			"	erp_warehouse cw " + 
			"WHERE" + 
			"	mfs.warehouse_id IN (${wareIds}) " +
			"	AND cw.id = mfs.materiel_id " + 
			"	${name}" + 
			"	AND mfs.materiel_flow_date >= #{startTime}" + 
			"	AND mfs.materiel_flow_date <= #{endTime}" + 
			"	AND ( mfs.materiel_retained != 1 OR mfs.materiel_Retained IS NULL ) " + 
			"	AND mfs.flow_subject != '车辆物料' " + 
			"GROUP BY" + 
			"	cw.materiel_name," + 
			"	cw.materiel_brand," + 
			"	cw.materiel_specifications," + 
			"	cw.materiel_model," + 
			"	cw.materiel_place," + 
			"	cw.materiel_state," + 
			"	cw.materiel_unit")
	public List<Map<String, Object>> selectByWareIdsAndTime(@Param("wareIds")String wareIds,@Param("name")String name,@Param("startTime")String startTime,@Param("endTime")String endTime);
	
	@Select("select SUM(mfs.materiel_storage_number - mfs.materiel_outflow_number) as num,  " + 
			"						mfs.materiel_id, " + 
			"						cus.cus_name,  " + 
			"						cusW.war_name  " + 
			"						from erp_materiel_flow_summary mfs,erp_warehouse cw ,erp_customer_warehouse cusW, erp_customer cus " + 
			"						where mfs.warehouse_id IN (${warId}) " + 
			"						and cw.id = mfs.materiel_id  " + 
			"						and cusW.id = mfs.warehouse_id and cus.id = cusW.customer_id  " + 
			"						and mfs.materiel_flow_date <= #{startTime}  " + 
			"						and ( mfs.materiel_retained != 1 or mfs.materiel_retained is null)  " + 
			"						and cw.materiel_name = #{name} " + 
			"						and cw.materiel_brand = #{brand} " + 
			"						and cw.materiel_specifications = #{spec} " + 
			"						and cw.materiel_model = #{model} " + 
			"						and cw.materiel_place = #{place} " + 
			"						and cw.materiel_state = #{state} " + 
			"						and cw.materiel_unit = #{unit} " + 
			"						and mfs.flow_subject != '车辆轮胎'  " + 
			"						GROUP BY mfs.materiel_id")
	public List<Map<String, Object>> selectByMaterielQiChu(@Param("warId")String warId,@Param("startTime")String startTime,@Param("name")String name,@Param("brand")String brand,@Param("spec")String spec,@Param("model")String model,@Param("place")String place,@Param("state")String state,@Param("unit")String unit);
	
	@Select("select SUM(mfs.materiel_storage_number) as rk,  " + 
			"                       SUM(mfs.materiel_outflow_number) as ck, "+
			"						mfs.materiel_id, " + 
			"						cus.cus_name,  " + 
			"						cusW.war_name  " + 
			"						from erp_materiel_flow_summary mfs,erp_warehouse cw ,erp_customer_warehouse cusW, erp_customer cus " + 
			"						where mfs.warehouse_id IN (${warId}) " + 
			"						and cw.id = mfs.materiel_id  " + 
			"						and cusW.id = mfs.warehouse_id and cus.id = cusW.customer_id  " + 
			"						and mfs.materiel_flow_date >= #{startTime}  " + 
			"						and mfs.materiel_flow_date <= #{endTime}  " + 
			"						and ( mfs.materiel_retained != 1 or mfs.materiel_retained is null)  " + 
			"						and cw.materiel_name = #{name} " + 
			"						and cw.materiel_brand = #{brand} " + 
			"						and cw.materiel_specifications = #{spec} " + 
			"						and cw.materiel_model = #{model} " + 
			"						and cw.materiel_place = #{place} " + 
			"						and cw.materiel_state = #{state} " + 
			"						and cw.materiel_unit = #{unit} " + 
			"						and mfs.flow_subject != '车辆轮胎'  " + 
			"						GROUP BY mfs.materiel_id")
	public List<Map<String, Object>> selectByMaterielChuRu(@Param("warId")String warId,@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("name")String name,@Param("brand")String brand,@Param("spec")String spec,@Param("model")String model,@Param("place")String place,@Param("state")String state,@Param("unit")String unit);
	
	@Select("select * from erp_materiel m where m.warehouse_id in (${wareIds}) and m.materiel_load = 0 AND m.id in (${materielIds}) ${sql}")
	public List<ErpMateriel> selectLikeProductionId(@Param("wareIds")String wareIds,@Param("materielIds")String materielIds,@Param("sql")String sql);
	
	@Select("select * from erp_materiel m where m.materiel_production_id in (${productionIds})")
	public List<ErpMateriel> selectInProductionId(@Param("productionIds")String productionIds);
	
	@Select("SELECT * from erp_materiel m where m.warehouse_id=#{warId}	and m.materiel_name=#{name}	and m.materiel_brand=#{brand} and m.materiel_specifications=#{spec} and m.materiel_model=#{model} and m.materiel_place=#{place} and m.materiel_state=#{state} and m.materiel_unit=#{unit} and m.materiel_load=0 and m.materiel_production_id='-1' limit 0,#{count}")
	public List<ErpMateriel> findPage(@Param("warId")int warId,@Param("name")String name,@Param("brand")String brand,@Param("spec")String spec,@Param("model")String model,@Param("place")String place,@Param("state")String state,@Param("unit")String unit,@Param("count")int count);
	
	@Select("SELECT * from erp_materiel m where m.warehouse_id in (${warId})	and m.materiel_name=#{name}	and m.materiel_brand=#{brand} and m.materiel_specifications=#{spec} and m.materiel_model=#{model} and m.materiel_place=#{place} and m.materiel_state=#{state} and m.materiel_unit=#{unit} and m.materiel_load=0  and materiel_storage_date>=#{startTime} and  materiel_storage_date<=#{endTime}")
	public List<ErpMateriel> selectWareHouseDateils(@Param("warId")String warId,@Param("name")String name,@Param("brand")String brand,@Param("spec")String spec,@Param("model")String model,@Param("place")String place,@Param("state")String state,@Param("unit")String unit,@Param("startTime")String startTime,@Param("endTime")String endTime);
	
	@Select("SELECT * from erp_materiel m where m.warehouse_id in (${warId}) and m.materiel_name=#{mat.materielName}	and m.materiel_brand=#{mat.materielBrand} and m.materiel_specifications=#{mat.materielSpecifications} and m.materiel_model=#{mat.materielModel} and m.materiel_place=#{mat.materielPlace} and m.materiel_state=#{mat.materielState} and m.materiel_unit=#{mat.materielUnit} and m.materiel_load=0 and m.materiel_production_id in (${sheng})")
	public List<ErpMateriel> selectSensor(@Param("warId")String warId,@Param("mat")ErpMateriel mat,@Param("sheng")String sheng);
	
	
}
